<?php
defined('_VALID_XTC') or die('Direct Access to this location is not allowed.');

require_once('includes/classes/class.categories.php');
require_once(DIR_FS_INC.'inc.get_tax_rate.php');
require_once(DIR_FS_INC.'inc.get_category_path.php');

require_once('includes/classes/'.FILENAME_IMAGEMANIPULATOR);

class ImportProducts {
  public $account_base = '/www/htdocs/w0109863/';
	public $clearFirst = false;
	public $testRun = false;
	public $sep_field = ';';
	public $sep_text = '"';
	public $language = 1;
	public $taxClass = 1;
	public $defStock = 0;
	public $root = 0;
	public $imgSrc = null;
	public $isGross = false;
	public $error = '';
	public $header = array();
	public $map = array();
	public $cat = null;
	public $mappingData = array( );
	
	public $logFileName = '';
	public $logFile = null;
	
	public function __construct() {
		global $db;
		$this->cat = new categories($db);
		$this->logFileName = DIR_FS_DOCUMENT_ROOT.'log_app/import_'.strftime('%Y%m%d').'.txt';
	}
	
	public function log($msg) {
		if ($this->logFile != null || $this->logFile = fopen($this->logFileName, 'a')) {
			fwrite($this->logFile, '['.strftime('%H:%M:%S').'] ' . $msg);
			fwrite($this->logFile, "\n");
    }
	}
	
	public function initDefault() {
    $this->mappingData['product'] = array(
      array('catFld' => 'products_status',                'default' => 1),
      array('catFld' => 'products_status_model',          'default' => 0),
      array('catFld' => 'products_tax_class_id',          'default' => $this->taxClass),
      array('catFld' => 'products_price',                 'default' => 0),
      array('catFld' => 'products_model',                 'default' => NULL),
      array('catFld' => 'products_quantity',              'default' => (int)$this->defStock),
      array('catFld' => 'products_weight',                'default' => 0),
      array('catFld' => 'products_image',                 'default' => NULL),
      array('catFld' => 'products_discount_allowed',      'default' => 0),
      array('catFld' => 'products_isbn',                  'default' => ''),
      array('catFld' => 'products_ean',                   'default' => ''),
      array('catFld' => 'brand_id',                       'default' => 0),
      array('catFld' => 'products_fsk18',                 'default' => 0),
      array('catFld' => 'products_usk',                   'default' => 0),
      array('catFld' => 'info_template',
                'thisFld' => 'product_template',          'default' => 'product_info_v1.html'),
      array('catFld' => 'options_template',               'default' => 'table_options.html'),
      array('catFld' => 'products_content_id',            'default' => 0),
      array('catFld' => 'products_vpe',                   'default' => 0),
      array('catFld' => 'products_vpe_status',            'default' => 0),
      array('catFld' => 'products_vpe_value',             'default' => 0),
      array('catFld' => 'shipping_status',
                'thisFld' => 'products_shippingtime',     'default' => 1),
      array('catFld' => 'products_shipping_costs',        'default' => 0),
      array('catFld' => 'products_sort',                  'default' => 0),
      array('catFld' => 'products_date_added',            'default' => 'now()'),
      array('catFld' => 'products_col_top',               'default' => 1),
      array('catFld' => 'products_col_left',              'default' => 1),
      array('catFld' => 'products_col_bottom',            'default' => 1),
      array('catFld' => 'products_info',                  'default' => 'tabs')
    );
  }
	
	public function process($filename)
	{
    $this->initDefault();	   

		$this->error = '';

	  $this->imgSrc = str_replace('\\', '/', $this->imgSrc);
		if ($this->imgSrc && !is_dir($this->imgSrc))
		  $this->imgSrc = $this->account_base.$this->imgSrc;

		if ($this->imgSrc && !is_dir($this->imgSrc)) {
			$this->error = "Bildverzeichnis nicht gefunden!";
			return false;
		}
		
		if (($f = fopen($filename, "r")) === FALSE) {
			$this->error = "Datei konnte nicht gelesen werden!";
			return false;
		}

		if ($this->processHeader($f) === FALSE) {
			$this->error = "Spaltenköpfe konnten nicht gelesen werden!";
			return false;
		}

		if ($this->mapping() === FALSE) {
			$this->error = "Spaltenzuordnung fehlerhaft!";
			return false;
		}

		if ($this->clearFirst) {
			$this->clearShop(-1);
		}

		$total = 0;
		while ($row = $this->processRow($f)) {
			if (!$this->processProduct($row, false)) {
				return false;
			}
			$total++;
			if ($this->testRun) return $total;
		}

		fclose($f);
		$this->garbageCollector();

		return $total;
	}

	public function processHeader($f) {
    return false;
	}

	/**
	* 'Clear' the shop by deactivating all unsused products and categories
	*/
	function clearShop($cat_id = -1) {
		global $db;
		// 1st - deactivate all products
		$db->db_query('UPDATE '.TABLE_PRODUCTS.' SET products_status = 0');
		// 2nd - deactivate all non-root categories
		$db->db_query('UPDATE '.TABLE_CATEGORIES.' SET categories_status = 0 WHERE parent_id <> 0');
	}	
	
	public function map($key, $column)
	{
		$lowColumn = strtolower($column);
		if (isset($this->header[$lowColumn])) {
			return $this->map[$key] = $this->header[$lowColumn];
		} else {
			$this->error = 'Die Spalte "'.$column.'" wurde nicht gefunden! Achten Sie auf die korrekte Schreibweise und vermeiten Sie Zeilenumbrüche.';
			$this->log("Spalte nicht erkannt: ".$column."\n");
			return -1;
		}
	}
	
	public function mapping() {
		if ($this->map('cat#1', 'Kategorie 1') == -1) return false;
		if ($this->map('cat#2', 'Kategorie 2') == -1) return false;
		if ($this->map('model', 'art.-nr.') == -1) return false;
		if ($this->map('name', 'Artikelname') == -1) return false;
		if ($this->map('desc', 'Artikelbeschreibung') == -1) return false;
		if ($this->map('price', 'Artikel Preis Brutto') == -1) return false;
		return true;
  }

	/**
	* Transform special chars like ä,ö,ü, ... to html codes "&#???;"
	* @param String $txt input text
	* @return transformed text
	*/
	public function specialChars($txt) {
return $txt;
		$res = ""; 
    	for ($i=0; $i < strlen($txt); $i++) { 
			$part = hexdec(rawurlencode($txt[$i]));
			if ($part == 196) $res .= 'Ä';
			else if ($part == 228) $res .= 'ä';
			else if ($part == 214) $res .= 'Ö';
			else if ($part == 246) $res .= 'ö';
			else if ($part == 220) $res .= 'Ü';
			else if ($part == 252) $res .= 'ü';
			else if ($part == 223) $res .= 'ß';
			else if ($part == 38) $res .= '&';
			else $res .= ($part <= 32 || $part > 1114111) ? $txt[$i] : "&#".$part.";";
		}
		return $res;
	}
	
	/**
	* Process the next csv row
	* @param File $f
	* @return mapped data array on success, false otherwise
	*/
	public function processRow($f)
	{
		if (($row = fgetcsv($f, 0, $this->sep_field, $this->sep_text)) !== false) {
		    $data = array();
			foreach ($this->map as $key => $val) {
				$val = $row[$val];
		        $val = trim($val);
		        $val = str_replace('""', '"', $val);
				$data[$key] = $val;
			}
			return $data;
		}
		return false;
	}	
	
	public function processProduct($data, $debug)
	{
		// Prepare Data
		$data['cat#1'] = $this->specialChars($data['cat#1']);
		$data['cat#2'] = $this->specialChars($data['cat#2']);
		$data['name'] = $this->specialChars($data['name']);
		$data['desc'] = preg_replace('!\s+!', ' ', $data['desc']);
		$data['desc'] = $this->specialChars($data['desc']);
		$data['price'] = str_replace('.', '', $data['price']);
		$data['price'] = (float)str_replace(',', '.', $data['price']);
		$data['image'] = str_replace('/', '-', $data['model']).'.JPG';
		
		// 1st ensure path
		$paths = array();
		if ($data['cat#1']) $paths[] = $data['cat#1'];
		if ($data['cat#2']) $paths[] = $data['cat#2'];
		$data['idCat'] = $this->ensurePath($paths);
		
		if ($debug) {
			echo "<pre>";
			print_r($data);
		}		
		
		// 2nd insert/update product and product description
		$product = array();
		$product['products_model'] = $data['model'];
		$product['products_price'] = $data['price'];
		$product['products_image'] = $data['image'];
		$product['products_name'] = $data['name'];
		$product['products_description'] = $data['desc'];
		$hit = $this->findProduct($data['model']);
		if ($hit) {
			$product['products_id'] = $this->updateProduct($hit, $product);
		} else {
			$product['products_id'] = $this->createProduct($product);
		}
		
		// 3rd product<->categorie link
		$this->productToCategory($product['products_id'], $data['idCat']);
		
		// 4th product image
		if ($this->imgSrc) {
			$this->handleImage($product['products_id'], $this->imgSrc.'/'.$data['image']);
		}
		
		if ($debug) {
			echo "</pre>";
		}

		return true;
	}

	public function garbageCollector() 
	{
		return true;
	}

	public function ensurePath($paths)
	{
		global $db;
		if (!$paths) {
			return false;
		}
		$idParent = (int)$this->root;
		foreach ($paths as $category) {
			$hit = $this->findCategory($category, $idParent);
			if (!$hit) {
				$data = array();
				$data['parent_id'] = $idParent;
				$data['categories_name'] = $category;
				$hit = $this->createCategory($data);
			} else {
				if ($this->clearFirst) {				
					$db->db_query("UPDATE ".TABLE_CATEGORIES." SET categories_status = 1 WHERE categories_id = ".(int)$hit);
				}
			}
			if (!$hit) {
				return false;
			}
			$idParent = $hit;
		}
		return $idParent;
	}


	public function findCategory($needle, $idParent = null)
	{
		global $db;
		$sql = 'SELECT * FROM '.TABLE_CATEGORIES.' c INNER JOIN '.TABLE_CATEGORIES_DESCRIPTION.' cd ';
		$sql .= 'ON c.categories_id = cd.categories_id';
		
		if (is_int($needle)) {
			$sql .= ' AND c.categories_id = '.(int)$needle;
		} else {
			$sql .= ' AND cd.categories_name = "'.addslashes($needle).'"';
		}
		if ($idParent !== null) {
			$sql .= ' AND c.parent_id = '.(int)$idParent;
		}
		if ($this->language) {
			$sql .= ' AND cd.language_id = '.(int)$this->language;
		}
    //echo '<li>',$sql,'</li>';
		$result = $db->db_query($sql);
		if (!$result->_numOfRows) {
			return false;
		};
		return $result->fields['categories_id'];
	}


	public function createCategory($data)
	{
		// item
		$cat_data = array(
			'sort_order' => 0,
			'status' => 1,
			'num' => 1,
			'products_sorting' => 'p.products_price',
			'products_sorting2' => 'ASC',
			'categories_template' => 'categorie_listing.html',
			'categories_col_top' => 1,
			'categories_col_bottom' => 1,
			'categories_col_left' => 1,
			'categories_col_right' => 0,
			'category_default_list' => 'product_listing_grid',
			'listing_template' => NULL
		);

		// desc
		$idLang = isset($data['language_id']) ? (int)$data['language_id'] : (int)$this->language;
		$cat_data['categories_name'][$idLang] = isset($data['categories_name']) ? $data['categories_name'] : "";
		$cat_data['categories_heading_title'][$idLang] = isset($data['categories_heading_title']) ? $data['categories_heading_title'] : "";
		$cat_data['categories_description_'.$idLang] = "";
		$cat_data['categories_description_2_'.$idLang] = "";
		$cat_data['categories_pic_alt'][$idLang] = "";
		$cat_data['categories_meta_title'][$idLang] = "";
		$cat_data['categories_meta_description'][$idLang] = "";
		$cat_data['categories_meta_keywords'][$idLang] = "";
		$cat_data['treepodia_1_'.$idLang] = '';
		$cat_data['treepodia_2_'.$idLang] = '';
		$cat_data['treepodia_3_'.$idLang] = '';
		$cat_data['treepodia_4_'.$idLang] = '';
		$cat_data['treepodia_5_'.$idLang] = '';		
		
		// new
		$data['categories_id'] = $this->cat->insert_category($cat_data, isset($data['parent_id']) ? $data['parent_id'] : 0, 'insert');
		
		if (MODULE_SEO_URL_INDEX_STATUS == 'True') {
			global $seo_url;
			$seo_url->insertSeoDBTable('category', $data['categories_id']);
		}	
		return $data['categories_id'];
	}
	
	
	public function findProduct($needle)
	{
		global $db;
		$sql = 'SELECT products_id FROM '.TABLE_PRODUCTS.' WHERE';
		if (is_int($needle)) {			
			$sql .= ' products_id = '.$needle;
		} else {
			$sql .= ' products_model="'.addslashes($needle).'"';
		}

		$result = $db->db_query($sql);
		if (!$result->_numOfRows) {
			return false;
		};
		return $result->fields['products_id'];
	}
	
	public function createProduct($data)
	{
		global $db;
		
		// item
		$pro_data = array();
		foreach($this->mappingData['product'] as $mappingItem) {
      $pro_data[$mappingItem['catFld']]
        = isset($data[(isset($mappingItem['thisFld']) ? $mappingItem['thisFld'] : $mappingItem['catFld'])])
        ? $data[(isset($mappingItem['thisFld']) ? $mappingItem['thisFld'] : $mappingItem['catFld'])]
        : $mappingItem['default'];
    }

		// desc
		$idLang = isset($data['language_id']) ? (int)$data['language_id'] : (int)$this->language;
		$pro_data['products_name'][$idLang] = isset($data['products_name']) ? addslashes($data['products_name']) : "";
		$pro_data['products_description_'.$idLang] = isset($data['products_description']) ? addslashes($data['products_description']) : NULL;
		$pro_data['products_short_description_'.$idLang] = isset($data['products_short_description']) ? addslashes($data['products_short_description']) : NULL;
		$pro_data['products_meta_title'][$idLang] = isset($data['products_meta_title']) ? $data['products_meta_title'] : "";
		$pro_data['products_meta_description'][$idLang] = isset($data['products_meta_description']) ? $data['products_meta_description'] : "";
		$pro_data['products_meta_keywords'][$idLang] = isset($data['products_meta_keywords']) ? $data['products_meta_keywords'] : "";
		$pro_data['products_keywords'][$idLang] = isset($data['products_keywords']) ? $data['products_keywords'] : NULL;
		$pro_data['products_url'][$idLang] = isset($data['products_url']) ? $data['products_url'] : NULL;
	
		$_POST['cPath'] = array();
		$pro_data['products_id'] = $this->cat->insert_product($pro_data, 0, 'insert');
		// update price (hack)
		if ($this->isGross == false) {
			$temp = array('products_price' => $data['products_price']);
			$db->db_perform(TABLE_PRODUCTS, $temp, 'update', 'products_id = '.(int)$pro_data['products_id']);
		}
				
		return $pro_data['products_id'];
	}

	
	public function updateProduct($idProduct, $data)
	{
		global $db;
		if (!$idProduct) {
			return false;
		}

		$pro_data = array();
    
    // product
		$pro_data_db = $db->db_query("
      SELECT *
      FROM ".TABLE_PRODUCTS."
			WHERE	products_id = '".$idProduct."'");
    if(!$pro_data_db->EOF) 
      $pro_data = $pro_data_db->fields;

		foreach($this->mappingData['product'] as $mappingItem) {
      if (isset($data[(isset($mappingItem['thisFld']) ? $mappingItem['thisFld'] : $mappingItem['catFld'])]))
        $pro_data[$mappingItem['catFld']] 
          = $data[(isset($mappingItem['thisFld']) ? $mappingItem['thisFld'] : $mappingItem['catFld'])];
    }

		// desc
		$idLang = isset($data['language_id']) ? (int)$data['language_id'] : (int)$this->language;
		$items_underlining = array(
      'products_description', 'products_short_description', 
      'treepodia_1', 'treepodia_2', 'treepodia_3', 'treepodia_4', 'treepodia_5');
		$pro_data_db = $db->db_query("
      SELECT *
      FROM ".TABLE_PRODUCTS_DESCRIPTION."
			WHERE	products_id = '".$idProduct."' and language_id = " . $idLang);
    if(!$pro_data_db->EOF) {
      foreach($pro_data_db->fields as $k => $v) {
        if (in_array($k, $items_underlining))
      		$pro_data[$k.'_'.$idLang] = $v;
      	else if($k == 'products_img_alt')
      		$pro_data['products_image_alt_'.$idLang] = $v;
      	else
      		$pro_data[$k][$idLang] = $v;
      }
    }

    if (isset($data['products_name'])) $pro_data['products_name'][$idLang] = addslashes($data['products_name']);    
    foreach(array('products_description', 'products_short_description') as $i) {
      if (isset($data[$i])) $pro_data[$i.'_'.$idLang] = addslashes($data[$i]);
    }
    foreach(array('products_meta_title', 'products_meta_description', 'products_meta_keywords', 'products_keywords', 'products_url') as $i) {
      if (isset($data[$i])) $pro_data[$i][$idLang] = $data[$i];  
    }

		$_POST['cPath'] = array();
		$pro_data['products_id'] = $idProduct;
		$this->cat->insert_product($pro_data, 0, 'update');
		
		// update price (hack)
		if ($this->isGross == false) {
			$temp = array('products_price' => $data['products_price']);
			$db->db_perform(TABLE_PRODUCTS, $temp, 'update', 'products_id = '.(int)$pro_data['products_id']);
		}

		return $idProduct;		
	}
	
	
	public function productToCategory($idProduct, $idCategory)
	{
		global $db;
		//if (!$idProduct || !$idCategory) {
		//	return false;
		//}
		// exists?
		$sql = 'SELECT products_id, categories_id FROM '.TABLE_PRODUCTS_TO_CATEGORIES.' WHERE products_id = '.(int)$idProduct.' AND categories_id = '.(int)$idCategory;
		$result = $db->db_query($sql);
		// echo '<li>'.$sql.'</li>';
		if ($result->_numOfRows == 1) {
			return true;
		};

		$data = array();
		// [product]
		$data['products_id'] = (int)$idProduct;
		$data['categories_id'] = (int)$idCategory;
		// print_r($data);
		$db->db_perform(TABLE_PRODUCTS_TO_CATEGORIES, $data);
		
		return true;		
	}
	
	public function handleImage($pid, $srcImage)
	{
		global $db;

		if (!file_exists($srcImage)) {
			return false;
		}
				
		$products_image_name = basename($srcImage);
		
		// copy($srcImage, DIR_FS_CATALOG_ORIGINAL_IMAGES.$products_image_name);
		
		// echo '<li> '.memory_get_peak_usage() / 1024 / 1024;
		// copy resized
		list($srcWidth, $srcHeight) = getimagesize($srcImage);
		$dstWidth = 1000;
		$dstHeight = round($srcHeight * $dstWidth / $srcWidth);
 		$srcImage= imagecreatefromjpeg($srcImage);	
		$dstImage = imagecreatetruecolor($dstWidth, $dstHeight);
    imagealphablending($dstImage, false);
    imagecopyresized($dstImage, $srcImage, 0, 0, 0, 0, $dstWidth, $dstHeight, $srcWidth, $srcHeight);
    @imagejpeg($dstImage, DIR_FS_CATALOG_ORIGINAL_IMAGES.$products_image_name);
		$srcImage = null; unset($srcImage);
		$dstImage = null; unset($dstImage);
		// echo '<li> '.memory_get_peak_usage() / 1024 / 1024;
		
		if (!file_exists(DIR_FS_CATALOG_ORIGINAL_IMAGES.$products_image_name)) {
			return false;
		}

		// run converters
		require(DIR_WS_INCLUDES.'product_icon_images.php');
		$a = null; unset($a);
		require(DIR_WS_INCLUDES.'product_thumbnail_images.php');
		$a = null; unset($a);
		require(DIR_WS_INCLUDES.'product_info_images.php');
		$a = null; unset($a);
		require(DIR_WS_INCLUDES.'product_popup_images.php');
		$a = null; unset($a);
		
		$db->db_query('UPDATE '.TABLE_PRODUCTS.' SET products_image = "'.$products_image_name.'" WHERE products_id = '.$db->db_prepare($pid));
		return true;
	}


	public static function getCategories()
	{
		$result = array();
		$result[] = array('id' => '0', 'text' => Wurzel);
		self::fillCategory($result, 0, 1);
		return $result;
	}


	public static function fillCategory(&$array, $idRoot, $level = 0)
	{
		global $db;
		$row = $categories = $db->db_query("SELECT c.categories_id AS id, cd.categories_name AS name FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd
			WHERE c.parent_id = '".$idRoot."'
			AND c.categories_id = cd.categories_id
			AND cd.language_id = '".(int)$_SESSION['languages_id']."'
			ORDER BY c.sort_order"
		);
		while (!$row->EOF) {
			$array[] = array ('id' => $row->fields['id'], 'text' => str_repeat('&nbsp;', 3 * $level) . $row->fields['name']);
			self::fillCategory($array, $row->fields['id'], $level + 1);
			$row->MoveNext();
		}
	}

}

class ImportProductsCSV extends ImportProducts {

	public function processHeader($f)
	{
		$this->header = array();
		$this->map = array();
		if (($cols = fgetcsv($f, 0, $this->sep_field, $this->sep_text)) !== FALSE) {
			for ($idx = 0; $idx < count($cols); $idx++) {
				$cols[$idx] = trim($cols[$idx]);
		        $cols[$idx] = str_replace('""', '"', $cols[$idx]);
				// $cols[$idx] = preg_replace("/^\"(.*)\"$/sim", "$1", $cols[$idx]);
				$cols[$idx] = strtolower($cols[$idx]);
				$this->header[$cols[$idx]] = $idx;
			}
			return true;
		}
		return false;
	}

	/**
	* Process the next csv row
	* @param File $f
	* @return mapped data array on success, false otherwise
	*/
	public function processRow($f)
	{
		if (($row = fgetcsv($f, 0, $this->sep_field, $this->sep_text)) !== false) {
		    $data = array();
			foreach ($this->map as $key => $val) {
				$val = $row[$val];
		        $val = trim($val);
		        $val = str_replace('""', '"', $val);
				$data[$key] = $val;
			}
			return $data;
		}
		return false;
	}

}

class ImportProductsExcel extends ImportProducts {
  var $importFileName = '';
  var $importFileType = '';
  var $fileType = '';
  var $sheet = array();
  var $pointerRow = 0;
  
  public function process($filename, $fileType) {
    $this->importFileName = $filename;
    $this->importFileType = $fileType;
    return parent::process($filename);
  }
  
	public function processHeader($f) {
    $this->_parseFile();

		$this->header = array();
		$this->map = array();
		$this->pointerRow = 1;
		if ($cols = $this->sheet[0]) {
			for ($idx = 0; $idx < count($cols); $idx++) {
				$cols[$idx] = trim($cols[$idx]);
				$cols[$idx] = str_replace('""', '"', $cols[$idx]);
				$cols[$idx] = strtolower($cols[$idx]);
				if ($cols[$idx] != '')
  				$this->header[$cols[$idx]] = $idx;
			}
			$this->log("Spalten zugeordnet\n" . print_r($this->header, true));
			return true;
		}
		return false;
  }

  function _parseFile() {

    if (strpos($this->importFileType, 'openxmlformats') !== false) {
      // xlsx
      include_once join(array(DIR_WS_CLASSES, 'io', 'excel', 'Simplexlsx', 'simplexlsx.class.php'), DS);

      $excelHandler = new SimpleXLSX($this->importFileName);
      $this->sheet = $excelHandler->rows();
      foreach($this->sheet as &$row) {
        foreach($row as &$col) {
          //$col = utf8_decode($col);
        }
      }
    }
    else {
      include_once join(array(FS_SRC, 'lib', 'io', 'xls', 'Reader', 'reader.php'), DS);
      // xls
    }
  }

}

class ImportProductsBM extends ImportProductsExcel {
  var $options = array();
  var $filters = array();
  var $filter_map = array();
  
  var $kind = '';
	var $type = '';
	
	protected function getProductTemplate() {
		$category_path = explode('_', get_category_path($this->root));
		$mt = array(
			'3|H' => 'product_model_stuhl.html',
			'3|K' => 'product_basis_stuhl.html',
			'2|H' => 'product_model_furniture.html',
			'2|K' => 'product_basis_furniture.html'
		);
		return $mt[$category_path[0].'|'.$this->kind];
	}

	public function mapping() {
		if ($this->map('#|-|model_parent', 'ARTIKELNUMMER_BASIS') == -1)
      $this->kind = 'K';
    else
      $this->kind = 'H';

		if ($this->map('#|-|width', 'Breite') != -1
					&& $this->map('#|-|deep', 'Tiefe') != -1
					&& $this->map('#|-|height', 'Höhe') != -1)
      $this->type = 'tisch';
    else
      $this->type = 'stuhl';

		$this->log('set kind(' . $this->kind . '/' . $this->type . ')');

 		if ($this->map('product|N|sort', 'Reihenfolge') == -1) return false;
 		if ($this->map('product|H|name', 'NAME') == -1) return false;
 		if ($this->map('product|S|model', 'ARTIKELNUMMER') == -1) return false;
 		if ($this->map('product|T|description', 'Text_HTML') == -1) return false;
 		if ($this->map('product|N|price', 'Preis') == -1) return false;
 		if ($this->map('product|N|shippingtime', 'Lieferzeit') == -1) return false;
 		if ($this->map('product|N|shipping_costs', 'Lieferpreis') == -1) return false;

    if ($this->kind == 'K') {
  		if ($this->map('#|-|cat', 'SHOPKATEGORIE') == -1) return false;
  		if ($this->map('#|-|groupdescr', 'IMAGETEXT') == -1) return false;
			if($this->type == 'tisch')
				if ($this->map('#|-|generate_tp_topic', 'TYPENPLANBEZEICHNUNG') == -1) return false;
			if ($this->map('#|-|enable_konfigurator', 'KONFIGURIERBAR') == -1) return false;
    }

    else if ($this->kind == 'H') {
      // nothing else
    }

		if ($this->type == 'stuhl') {
			if ($this->map('#|-|img', 'BILDER') == -1) return false;
		}
		
		if ($this->type == 'tisch') {
			if ($this->kind == 'H') {
				if ($this->map('#|-|img', 'BILDER') == -1) return false;
			}
			else {
				if ($this->map('#|t|img_col', 'ÜBERSICHTSBILD') == -1) return false;
				if ($this->map('#|p|img_col', 'HAUPTBILD PRODUKTANSICHT') == -1) return false;
				if ($this->map('#|1|img_col', 'BILD 1 PRODUKTANSICHT') == -1) return false;
				if ($this->map('#|2|img_col', 'BILD 2 PRODUKTANSICHT') == -1) return false;
				if ($this->map('#|3|img_col', 'BILD 3 PRODUKTANSICHT') == -1) return false;
				if ($this->map('#|4|img_col', 'BILD 4 PRODUKTANSICHT') == -1) return false;
				if ($this->map('#|-|productgroup', 'TYPENPLANNUMMER') == -1) return false;
				if ($this->map('#|-|abmessungbasis', 'ABMESSUNG_BASIS') == -1) return false;
				if (
					($this->map('#|-|formid_tisch', 'TISCHFORM') == -1 || $this->map('#|-|filterid_tisch', 'FILTER_TISCHFORM') == -1)
					&& ($this->map('#|-|formid_container', 'CONTAINERAUFBAU') == -1  || $this->map('#|-|filterid_container', 'FILTER_CONTAINERAUFBAU') == -1)
					&& ($this->map('#|-|formid_schrank', 'SCHRANKFORM') == -1  || $this->map('#|-|filterid_schrank', 'FILTER_SCHRANKFORM') == -1)
					&& ($this->map('#|-|formid_zuboehr', 'ZUBEHOERFORM') == -1)  
					&& ($this->map('#|-|formid_theke', 'THEKENFORM') == -1)
					&& ($this->map('#|-|formid_konftisch', 'KONFTISCHFORM') == -1)
				) return false;
				if ($this->map('#|-|programid', 'MOEBELPROGRAMM') == -1) return false;
				if ($this->map('#|-|description_size', 'Breite') == -1) return false;
			}
		}

		return true;
  }
	
		/**
	* deaktivate the shop by deactivating all unsused products and categories in parent folder
	*/
	function clearShop($cat_id = -1) {
		global $db;
		$this->log('clear shop: Kategorie ' . $cat_id . ' ('. $this->root . ')..');
		
		if ($cat_id == -1) 
			$cat_id = $this->root;
		else {
			// 1st - deactivate category - disabled
			$sql = ("UPDATE ".TABLE_CATEGORIES." SET categories_status = 0 WHERE categories_id = '".$cat_id."'");
			//echo_e($sql);
			//$this->log('[1] '.$sql);
//			$db->db_query($sql);
		}

		// 2st - deactivate all products in category
		$sql = ("
			update 
				".TABLE_PRODUCTS." p
				inner join ".TABLE_PRODUCTS_TO_CATEGORIES." p2c on p.products_id = p2c.products_id
			set
				p.products_status = 0
			where
				p2c.categories_id = '".$cat_id."'");
		echo_e($sql);
		$this->log('[2] '.$sql);
//		$db->db_query($sql);

		// iterate child categories
		$childs = array();
		$sql = "SELECT categories_id FROM ".TABLE_CATEGORIES." WHERE parent_id = '".$cat_id."'";
    $query = $db->db_query($sql, false);

		if($query->_numOfRows > 0){
			while(!$query->EOF){
        $childs[] = $query->fields['categories_id'];
        $query->MoveNext();
			}
		}
		
		$this->log('iterate childs: ' . implode(', ', $childs));
		foreach($childs as $child) $this->clearShop($child);
	}	

  public function processOptions() {
    $row = $this->sheet[1];
    
    $_header = $this->header;
    asort($_header);

    foreach($_header as $name => $colIdx) {
      if ($name == 'filter') {
        while(
          $colIdx < count($row)
          && ($this->sheet[0][$colIdx] == '' || strtolower($this->sheet[0][$colIdx]) == 'filter')
        ) 
        {
          $flt = trim($row[$colIdx]);
					$this->filters[$colIdx] = $flt;
          $colIdx++;
        }
 
        foreach($this->filters as &$v) {
          $v = $this->getFilterId($v);
        }
      }
    
      else if ($row[$colIdx] == 'Notwendigkeit') {
        $option = array(
          'header'  => $name,
          'name'    => $this->sheet[0][$colIdx],
          'option'  => 'HAVE_TO'
        );
        $this->options[$colIdx] = $option;

        $colIdx++;
        while(
          $colIdx < count($row)
          && $this->sheet[0][$colIdx] == '' 
          && $row[$colIdx] != 'Notwendigkeit') 
        {
          if (trim($row[$colIdx]) != '') {
            $option['option'] = $row[$colIdx];
            $this->options[$colIdx] = $option;
          }
          $colIdx++;
        }
        
        unset($this->header[$name]);
      }
    }
    $this->pointerRow = 2;
		$this->log("Spalten erkennen:\n"
						.  "Options: ".print_r($this->options, true)."\n"
						.  "Filter: ".print_r($this->filters, true)."\n"
						.  "sonstige: ".print_r($this->header, true)."\n");
  }

	/**
	* Process the next row
	* @param File $f
	* @return mapped data array on success, false otherwise
	*/
	public function processRow($f) {
		if ($this->pointerRow == 1) $this->processOptions();

		if ($this->pointerRow >= count($this->sheet)) return false;
		if (trim($this->sheet[$this->pointerRow][0]) == '') return false;

		$row = $this->sheet[$this->pointerRow];
		$this->log("import Zeile " . $this->pointerRow . " .. ");

		$data = array();
		foreach ($this->map as $key => $val) {
			$val = $row[$val];
			$val = trim($val);
			$val = str_replace('""', '"', $val);
			$data[$key] = $val;
		}

		foreach ($this->options as $colIdx => $val) {
			$val = $row[$colIdx];
			$val = trim($val);
			$data['option|-|'.$colIdx] = $val;
		}

		foreach ($this->filters as $colIdx => $val) {
			$val = $row[$colIdx];
			$val = trim($val);
			$data['filter|-|'.$colIdx] = $val;
		}
		$this->log("data " . print_r($data, true));
		//echo "<hr>".$this->pointerRow . ":\n";
//echo_e($data, 'E');
		$this->pointerRow++;
		return $data;
	}

	public function processProduct($data, $debug) {
  	global $db;
  	//$debug = true;
  	$product = array();
  	$images = array();
  	$options = array();
  	$filters = array();

  	$product['product_template'] = $this->getProductTemplate();
    $product['products_status'] = 1;
  	$product['products_status_model'] = ($this->kind == 'H' ? 1 : 0);

    foreach($data as $key => $val) {
      list($group, $type, $name) = explode('|', $key);

      if ($type == 'S') //tring
        $val = ($val);
      else if ($type == 'H') //TML
        $val = $this->specialChars($val);
      else if ($type == 'T') { //ext
        $val = preg_replace('!\s+!', ' ', $val);
        $val = $this->specialChars($val);
      }
      else if ($type == 'N') { //umeric
				if (!is_numeric($val)) {
					$val = str_replace('.', '', $val);
					$val = (float)str_replace(',', '.', $val);
				}
      }

      if ($group == 'product') {
        $product['products_'.$name] = $val;
      }

      else if($group == '#') {
        if ($name == 'img') {
          if(file_exists($this->imgSrc.DS.$val)) {
            $pathinfo = pathinfo($this->imgSrc.DS.$val);
            $images[] = $this->imgSrc.DS.$val;
            $images_more = glob($pathinfo['dirname'].DS.$pathinfo['filename'].'_*.'.$pathinfo['extension']);
            if (is_array($images_more))
              $images = array_merge($images, $images_more);
          }
					$this->log('find images: ' . print_r($images, true));
        }

        if ($name == 'img_col') {
					$images[$type] = $this->imgSrc.DS.$val;
				}

        else if ($name == 'cat') {
          $data['category_id'] = array();
          foreach(explode(',', $val) as $path_val) {
            if (trim($path_val) != '') {
              $paths = explode('/', $path_val);
              $data['category_id'][] = $this->ensurePath($paths);
            }
          }
					$this->log('find categories: ' . print_r($data['category_id'], true));
        }

        else if ($name == 'model_parent') {
          $data[$name.'_id'] = $this->getProductIdByModel($val);
          $data['category_id'] = $this->_getCategories($data[$name.'_id']);
					$this->log('find basis model: pid-'.$data[$name.'_id'].', cid-'.$data['category_id']);
        }
				
				else if  ($name == 'description_size') {
					$product['products_description'] .= 
					'</div>
						<div class="prod_description_basis_itdotmedia_titel">
							Maße (BxTxH): <span style="font-family:sintonyregular">'
								.$data['#|-|width']. ' x ' .$data['#|-|deep']. ' x ' .$data['#|-|height']. 
								' cm</span>
						</div>
					<div>';
				}
      }

      else if ($group == 'option') {
        $option = array(
          'group'   => $this->options[$name]['name'],
          'option'  => $this->options[$name]['option'],
          'default' => $val == 'x' ? 'true' : 'false',
          'value'   => ($val == 'x' || $val == '') ? 'null' : $val
        );
        if ($option['option'] == 'HAVE_TO' || $option['default'] == 'true' || $option['value'] != 'null')
          $options[] = $option;
      }
      else if ($group == 'filter') {
        if ($val != '') $filters[] = $this->filters[$name];
      }
    }

		$this->log('options: '.print_r($options, true));
		$this->log('filters: '.print_r($filters, true));
		$this->log('images: '.print_r($images, true));
		$this->log('product: '.print_r($product, true));
		$this->log('data: '.print_r($data, true));

    if ($debug) {
      echo "<pre>";
      echo_e($options);
      echo_e($filters);
      echo_e($images);
      echo_e($product);
      echo_e($data);
      die('programming in process ..');
    }

		// insert/update product and product description
		$hit = $this->findProduct($product['products_model']);
		if ($hit) {
		  if ($debug) echo "\nfind product: ".$hit;
			$this->log('find product: '.$hit);
			$product['products_id'] = $this->updateProduct($hit, $product);
		} else {
		  if ($debug) echo "\nadd product";
			$this->log('add product ..');
			$product['products_id'] = $this->createProduct($product);
		  if ($debug) echo "\nnew ID: " . $product['products_id'];
			$this->log('add product: ' . $product['products_id']);
		}
	  if ($debug) echo "\nproducts_id: " . $product['products_id'];
		$this->log('products_id: ' . $product['products_id']);

		// product<->categorie link
		foreach($data['category_id'] as $cat)
			$this->productToCategory($product['products_id'], $cat);

		// productseo
		if(MODULE_SEO_URL_INDEX_STATUS == 'True') {
			global $seo_url;
			$seo_url->refreshSeoDBTable('product', $product['products_id']);
		}

		// 4th product image
		// clear when update ... 
		foreach($images as $idx => $image) {
      set_time_limit(30);
			if ($idx == 'p') $idx=0;
			$img_name = $this->cat->insert_product_image($image, $product['products_id'], $product['products_name'], $idx);
		  if ($debug) echo "\nprocess image: ".$image;
			$this->log('process image: '.$image. ' => ' . $product['products_image']);

      if ($idx === 0) {
				$product['products_image'] = $img_name;
        $temp = array('products_image' => $product['products_image']);
        if (!isset($images['t'])) {
					$temp['products_image_tile'] = $product['products_image_tile'] = $img_name;
				}
				$db->db_perform(TABLE_PRODUCTS, $temp, 'update', 'products_id = '.$product['products_id']);
				$db->db_query("delete from ".TABLE_PRODUCTS_IMAGES." WHERE products_id = ".$product['products_id']);
      }
      else if ($idx == 't') {
				$product['products_image_tile'] = $img_name;
        $temp = array('products_image_tile' => $product['products_image_tile']);
        $db->db_perform(TABLE_PRODUCTS, $temp, 'update', 'products_id = '.$product['products_id']);
      }
      else {
        $temp = array(
          'image_name' => $img_name,
          'products_id' => $product['products_id'],
          'image_nr' => $idx
        );
   			$db->db_perform(TABLE_PRODUCTS_IMAGES, $temp, 'insert');
      }
		}

    $option_included = array();
    
    if ($this->kind == 'K') {
      // 5th options
      if ($debug) echo "\nprocess options ..";
      $this->log('process options ..');
      $db->db_query("delete from ".TABLE_KONFIGURATOR_PRODUCT_OPTION." WHERE product_id = ".$product['products_id']);
      foreach($options as $option) {
        $option_included[] = $option['group'];
        $sql_insert_data = array(
          'product_id' => $product['products_id'],
          'konfigurator_option_group' => $option['group'],
          'konfigurator_option' => $option['option'],
          'is_default' => $option['default'],
          'price' => $option['value']
        );
        $db->db_perform(TABLE_KONFIGURATOR_PRODUCT_OPTION, $sql_insert_data, 'insert');
      }
		
      // 5.2 options Bezugstoffe
      //print_r($option_included);
      if ($debug) echo "\nprocess option bezugstoffe ..";
      $option_included = array_unique($option_included);
      if (in_array('BEZUGSTOFFE_1', $option_included)
          && in_array('BEZUGSTOFFE_2', $option_included)) {

        foreach(array(array('F', '1'), array('R', 2)) as $v) {
          //      	echo("
          $db->db_query("
            UPDATE
              ".TABLE_KONFIGURATOR_PRODUCT_OPTION."
            SET
              konfigurator_option = concat('".$v[0]."|', konfigurator_option)
            WHERE
              product_id = ".$product['products_id']."
              and konfigurator_option <> 'HAVE_TO'
              and konfigurator_option_group = 'BEZUGSTOFFE_".$v[1]."'");
        }
        //      	echo("
        $db->db_query("
          delete from ".TABLE_KONFIGURATOR_PRODUCT_OPTION."
          WHERE
            product_id = ".$product['products_id']."
            and konfigurator_option = 'HAVE_TO'
            and konfigurator_option_group = 'BEZUGSTOFFE_1'");
        //      	echo("
        $db->db_query("
          UPDATE ".TABLE_KONFIGURATOR_PRODUCT_OPTION."
          SET konfigurator_option_group = 'BEZUGSTOFFE_2'
          WHERE
            product_id = ".$product['products_id']."
            and konfigurator_option_group = 'BEZUGSTOFFE_1'");
      }

      // 6th sonst
			$options_add = array(
				'DESCRIPTION_GROUP' => $data['#|-|groupdescr'],
				'KONFIGURATOR_ENABLE' => ($data['#|-|enable_konfigurator'] == 'x' ? $this->type : 'false')
			);

			if(isset($data['#|-|abmessungbasis'])) $options_add['ABMESSUNG_BASIS'] = $data['#|-|abmessungbasis'];
			if(isset($data['#|-|generate_tp_topic'])) $options_add['TYPENPLAN_TOPIC'] = $this->_getSizeTopic($data);
				if(!$options_add['TYPENPLAN_TOPIC']) unset($options_add['TYPENPLAN_TOPIC']);

			if(isset($data['#|-|formid_tisch'])) $options_add['TISCHFORM'] = $data['#|-|formid_tisch'];
			if(isset($data['#|-|filterid_tisch'])) $options_add['FILTER_TISCHFORM'] = $data['#|-|filterid_tisch'];
			
			if(isset($data['#|-|formid_container'])) $options_add['CONTAINERFORM'] = $data['#|-|formid_container'];
			if(isset($data['#|-|filterid_container'])) $options_add['FILTER_CONTAINERFORM'] = $data['#|-|filterid_container'];

			if(isset($data['#|-|formid_schrank'])) $options_add['SCHRANKFORM'] = $data['#|-|formid_schrank'];
			if(isset($data['#|-|formid_schrank'])) {
				// Filter Hoehe bei Schrank direkt aus BASIS Abmessung
				$d = explode('_', $options_add['ABMESSUNG_BASIS']);
				if (preg_match('/[0-9]{1}OH/', $d[1], $match))
					$options_add['FILTER_SCHRANKHOEHE'] = 'FL_RGH_'.$d[1];
			}
			if(isset($data['#|-|filterid_schrank'])) $options_add['FILTER_SCHRANKFORM'] = $data['#|-|filterid_schrank'];

			if(isset($data['#|-|formid_zuboehr'])) $options_add['ZUBEHOERFORM'] = $data['#|-|formid_zuboehr'];
			if(isset($data['#|-|formid_theke'])) $options_add['THEKENFORM'] = $data['#|-|formid_theke'];
			if(isset($data['#|-|formid_konftisch'])) $options_add['KONFTISCHFORM'] = $data['#|-|formid_konftisch'];
			
			if(isset($data['#|-|programid'])) $options_add['MOEBELPROGRAMM'] = $data['#|-|programid'];
			if(isset($data['#|-|productgroup'])) $options_add['PRODUCTGROUP'] = $data['#|-|productgroup'];

      foreach($options_add as $k => $v) {
				if(!empty($v)) {
					$sql_insert_data = array(
						'product_id' => $product['products_id'],
						'konfigurator_option_group' => $k,
						'konfigurator_option' => $v,
						'is_default' => 'false',
						'price' => null
					);
					$db->db_perform(TABLE_KONFIGURATOR_PRODUCT_OPTION, $sql_insert_data, 'insert');
				}
			}

      // 7th filters
      $filters = array_unique($filters);
      $db->db_query("
        delete from ".TABLE_PRODUCTS_TO_FILTERS."
        WHERE
          products_id = ".$product['products_id']);
      foreach($filters as $filter_id) {
        $sql_insert_data = array(
          'products_id' => $product['products_id'],
          'filter_id' => $filter_id
        );
        $db->db_perform(TABLE_PRODUCTS_TO_FILTERS, $sql_insert_data, 'insert');
      }

    }
    else if ($this->kind == 'H') {
      // 8th parent moduls
      $this->log('refresh '.TABLE_PRODUCTS_TO_MODELS.' ..');
      $db->db_query("delete from ".TABLE_PRODUCTS_TO_MODELS." WHERE products_model = ".$product['products_id']);
      $sql_insert_data = array(
        'products_id' => $data['model_parent_id'],
        'products_model' => $product['products_id']
      );
      $db->db_perform(TABLE_PRODUCTS_TO_MODELS, $sql_insert_data, 'insert');
    }
    
		if ($debug) {
			echo "</pre>";
		}
		return true;
	}

	public function garbageCollector() 
	{
		global $db;
		$sql = "
			delete
				kpo
			from 
				".TABLE_KONFIGURATOR_PRODUCT_OPTION." kpo
				left join ".TABLE_PRODUCTS." p on kpo.product_id = p.products_id
			where
				p.products_id is null";
		$result = $db->db_query($sql);
		return true;
	}

	function getProductIdByModel($products_model) {
		global $db;
		$sql = "
			select products_id from ".TABLE_PRODUCTS."
			where products_model = '".$products_model."'";
		$result = $db->db_query($sql);
		if ($result->_numOfRows)
			return $result->fields['products_id'];
		else
			return false;
  }

	function _getCategories($products_id) {
		global $db;
		$categories = array();
		$sql = "
			select categories_id from ".TABLE_PRODUCTS_TO_CATEGORIES."
			where products_id = '".$products_id."'";
		$result = $db->db_query($sql);
		if ($result->_numOfRows) {
      while(!$result->EOF) {
        $categories[] = $result->fields['categories_id'];
        $result->moveNext();
      }
      return $categories;
    }
		else
			return false;
  }

	function _getSizeTopic($data) {
		$size_values = array();
		$size_units = array();

		if(empty($data['#|-|generate_tp_topic'])) return false;

		if(trim($data['#|-|generate_tp_topic']) == '') 
			return false;
		else if(trim($data['#|-|generate_tp_topic']) == 'x') {
			if (isset($data['#|-|width'])) {
				$size_values[] = $data['#|-|width'];
				$size_units[] = 'B';
			}
				
			if (isset($data['#|-|deep'])) {
				$size_values[] = $data['#|-|deep'];
				$size_units[] = 'T';
			}
			if (isset($data['#|-|height'])) {
				$size_values[] = $data['#|-|height'];
				$size_units[] = 'H';
			}
			$d = implode(' x ', $size_values) . '<br>' . implode('x', $size_units) . ' in cm';
		}
		else
			$d = $data['#|-|generate_tp_topic'].'<br>';

		return $d;
	}

	function getFilterId($filter_name, $parent_id = '', $ebene=0) {
		global $db;
		$sql = "
			select * from ".TABLE_PRODUCTS_FILTERS." 
			where filter_option_id = '".$filter_name."'";
		$result = $db->db_query($sql);
		if ($result->_numOfRows)
			return $result->fields['filter_id'];
		else
			return false;
		/*
		if ($ebene+1 < count($filter_items))
			return $this->getFilterId($filter_name, $result->fields['filter_id'], $ebene+1);
		else


		$filter_items = explode('|', $filter_name);
		if ($ebene >= count($filter_items)) return false; 	 

		$filter_items[$ebene] = str_replace('_', ' ', $filter_items[$ebene]);

		$sql = "
			select * from ".TABLE_PRODUCTS_FILTERS." 
			where filter_name = '".$filter_items[$ebene]."'";
		if ($parent_id!='') $sql .= " and parent_id = '".$parent_id."'";      
		$result = $db->db_query($sql);
		if (!$result->_numOfRows) {
			return false;
		};

		if ($ebene+1 < count($filter_items))
			return $this->getFilterId($filter_name, $result->fields['filter_id'], $ebene+1);
		else
			return $result->fields['filter_id'];
		*/			

  }

}
?>